package query

const (
	mysqlQueryWithServiceNames = `
        SELECT qc.checksum AS sql_id,
			   qc.fingerprint,
			   qe.query    AS example,
			   qe.db       AS db_name,
			   m.exec_count,
			   m.total_exec_time,
			   m.avg_exec_time,
			   m.rows_examined_max
		FROM (
				 SELECT qcm.query_class_id,
						SUM(qcm.query_count)                                        AS exec_count,
						TRUNCATE(SUM(qcm.query_time_sum), 2)                        AS total_exec_time,
						TRUNCATE(SUM(qcm.query_time_sum) / SUM(qcm.query_count), 2) AS avg_exec_time,
						MAX(qcm.rows_examined_max)                                  AS rows_examined_max
				 FROM query_class_metrics qcm
						  INNER JOIN instances i ON qcm.instance_id = i.instance_id
				 WHERE i.name IN (%s)
				   AND qcm.start_ts >= ?
				   AND qcm.start_ts < ?
				   AND qcm.rows_examined_max >= ?
				   AND qcm.query_time_sum / qcm.query_count >= ?
				 GROUP BY qcm.query_class_id
				 ORDER BY avg_exec_time DESC
				 LIMIT ? OFFSET ?) m
				 INNER JOIN query_classes qc ON m.query_class_id = qc.query_class_id
				 LEFT JOIN query_examples qe ON m.query_class_id = qe.query_class_id;
    `
	mysqlQueryWithDBName = `
        SELECT qc.checksum AS sql_id,
               qc.fingerprint,
               qe.query    AS example,
               qe.db       AS db_name,
               m.exec_count,
               m.total_exec_time,
               m.avg_exec_time,
               m.rows_examined_max
        FROM (
                 SELECT qcm.query_class_id,
                        SUM(qcm.query_count)                                        AS exec_count,
                        TRUNCATE(SUM(qcm.query_time_sum), 2)                        AS total_exec_time,
                        TRUNCATE(SUM(qcm.query_time_sum) / SUM(qcm.query_count), 2) AS avg_exec_time,
						MAX(qcm.rows_examined_max)                                  AS rows_examined_max
                 FROM query_class_metrics qcm
                          INNER JOIN instances i ON qcm.instance_id = i.instance_id
                 		  INNER JOIN query_examples qe ON qcm.query_class_id = qe.query_class_id
                 WHERE i.name IN (%s)
				   AND qe.db = ?
                   AND qcm.start_ts >= ?
                   AND qcm.start_ts < ?
				   AND qcm.rows_examined_max >= ?
				   AND qcm.query_time_sum / qcm.query_count >= ?
                 GROUP BY qcm.query_class_id
                 ORDER BY avg_exec_time DESC
				  LIMIT ? OFFSET ?) m
                 INNER JOIN query_classes qc ON m.query_class_id = qc.query_class_id
                 LEFT JOIN query_examples qe ON m.query_class_id = qe.query_class_id;
    `
	mysqlQueryWithSQLID = `
        SELECT qc.checksum AS sql_id,
               qc.fingerprint,
               qe.query    AS example,
               qe.db       AS db_name,
               m.exec_count,
               m.total_exec_time,
               m.avg_exec_time,
               m.rows_examined_max
        FROM (
                 SELECT qcm.query_class_id,
                        SUM(qcm.query_count)                                        AS exec_count,
                        TRUNCATE(SUM(qcm.query_time_sum), 2)                        AS total_exec_time,
                        TRUNCATE(SUM(qcm.query_time_sum) / SUM(qcm.query_count), 2) AS avg_exec_time,
						MAX(qcm.rows_examined_max)                                  AS rows_examined_max
                 FROM query_class_metrics qcm
                          INNER JOIN instances i ON qcm.instance_id = i.instance_id
						  INNER JOIN query_classes qc ON qcm.query_class_id = qc.query_class_id
                 WHERE i.name IN (%s)
				   AND qc.checksum = ?
                   AND qcm.start_ts >= ?
                   AND qcm.start_ts < ?
                 GROUP BY query_class_id) m
                 INNER JOIN query_classes qc ON m.query_class_id = qc.query_class_id
                 LEFT JOIN query_examples qe ON m.query_class_id = qe.query_class_id
        LIMIT 1;
    `
	clickhouseQueryWithServiceNames = `
        SELECT sm.sql_id,
			   m.fingerprint,
			   m.example,
			   m.db_name,
			   sm.exec_count,
			   sm.total_exec_time,
			   sm.avg_exec_time,
			   sm.rows_examined_max
		FROM (
			SELECT queryid                                               AS sql_id,
				   SUM(num_queries)                                      AS exec_count,
				   TRUNCATE(SUM(m_query_time_sum), 2)                    AS total_exec_time,
				   TRUNCATE(SUM(m_query_time_sum) / SUM(num_queries), 2) AS avg_exec_time,
				   IF(MAX(m_rows_examined_max) = 0 AND SUM(m_rows_examined_cnt) != 0,
					  SUM(m_rows_examined_sum) / SUM(m_rows_examined_cnt),
					  MAX(m_rows_examined_max))                          AS rows_examined_max
			FROM metrics
			WHERE service_type = 'mysql'
			  AND service_name IN (%s)
			  AND period_start >= ?
			  AND period_start < ?
			  AND m_rows_examined_max >= ?
			  AND IF(m_query_time_max is null or m_query_time_max = 0,
					 m_query_time_sum / m_query_time_cnt,
					 m_query_time_max) >= ?
			GROUP BY queryid
			ORDER BY avg_exec_time DESC
			LIMIT ? OFFSET ?) sm
			LEFT JOIN (SELECT queryid                        AS sql_id,
							  MAX(fingerprint)               AS fingerprint,
							  MAX(example)                   AS example,
							  IF(MAX(database) is null or MAX(database) = '',
								 MAX(schema), MAX(database)) AS db_name
					   FROM metrics
					   WHERE service_type = 'mysql'
						 AND service_name IN (%s)
						 AND period_start >= ?
						 AND period_start < ?
						 AND m_rows_examined_max >= ?
			  			 AND IF(m_query_time_max is null or m_query_time_max = 0,
								m_query_time_sum / m_query_time_cnt,
								m_query_time_max) >= ?
					   GROUP BY queryid) m
					  ON sm.sql_id = m.sql_id;
    `
	clickhouseQueryWithDBName = `
		SELECT sm.sql_id,
			   m.fingerprint,
			   m.example,
			   m.db_name,
			   sm.exec_count,
			   sm.total_exec_time,
			   sm.avg_exec_time,
			   sm.rows_examined_max
		FROM (
			SELECT queryid                                               AS sql_id,
				   SUM(num_queries)                                      AS exec_count,
				   TRUNCATE(SUM(m_query_time_sum), 2)                    AS total_exec_time,
				   TRUNCATE(SUM(m_query_time_sum) / SUM(num_queries), 2) AS avg_exec_time,
				   IF(MAX(m_rows_examined_max) = 0 AND SUM(m_rows_examined_cnt) != 0,
					  SUM(m_rows_examined_sum) / SUM(m_rows_examined_cnt),
					  MAX(m_rows_examined_max))                          AS rows_examined_max
			FROM metrics
			WHERE service_type = 'mysql'
			  AND service_name IN (%s)
			  AND (database = ? OR schema = ?)
			  AND period_start >= ?
			  AND period_start < ?
			  AND m_rows_examined_max >= ?
			  AND IF(m_query_time_max is null or m_query_time_max = 0,
					 m_query_time_sum / m_query_time_cnt,
					 m_query_time_max) >= ?
			GROUP BY queryid
			ORDER BY avg_exec_time DESC
			LIMIT ? OFFSET ?) sm
			LEFT JOIN (SELECT queryid                        AS sql_id,
							  MAX(fingerprint)               AS fingerprint,
							  MAX(example)                   AS example,
							  IF(MAX(database) is null or MAX(database) = '',
								 MAX(schema), MAX(database)) AS db_name
					   FROM metrics
					   WHERE service_type = 'mysql'
						 AND service_name IN (%s)
						 AND (database = ? OR schema = ?)
						 AND period_start >= ?
						 AND period_start < ?
						 AND m_rows_examined_max >= ?
						 AND IF(m_query_time_max is null or m_query_time_max = 0,
								m_query_time_sum / m_query_time_cnt,
								m_query_time_max) >= ?
					   GROUP BY queryid) m
					  ON sm.sql_id = m.sql_id;
    `
	clickhouseQueryWithSQLID = `
        SELECT sm.sql_id,
			   m.fingerprint,
			   m.example,
			   m.db_name,
			   sm.exec_count,
			   sm.total_exec_time,
			   sm.avg_exec_time,
			   sm.rows_examined_max
		FROM (
			SELECT queryid                                               AS sql_id,
				   SUM(num_queries)                                      AS exec_count,
				   TRUNCATE(SUM(m_query_time_sum), 2)                    AS total_exec_time,
				   TRUNCATE(SUM(m_query_time_sum) / SUM(num_queries), 2) AS avg_exec_time,
				   IF(MAX(m_rows_examined_max) = 0 AND SUM(m_rows_examined_cnt) != 0,
					  SUM(m_rows_examined_sum) / SUM(m_rows_examined_cnt),
					  MAX(m_rows_examined_max))                          AS rows_examined_max
			FROM metrics
			WHERE service_type = 'mysql'
			  AND service_name IN (%s)
			  AND queryid = ?
			  AND period_start >= ?
			  AND period_start < ?
			GROUP BY queryid) sm
			LEFT JOIN (SELECT queryid                        AS sql_id,
							  MAX(fingerprint)               AS fingerprint,
							  MAX(example)                   AS example,
							  IF(MAX(database) is null or MAX(database) = '',
								 MAX(schema), MAX(database)) AS db_name
					   FROM metrics
					   WHERE service_type = 'mysql'
						 AND service_name IN (%s)
						 AND queryid = ?
						 AND period_start >= ?
						 AND period_start < ?
					   GROUP BY queryid) m
					  ON sm.sql_id = m.sql_id;
    `
)
